from .models import User, Movie, Rating
from django.contrib.auth.hashers import make_password
import pandas as pd
import datetime
import random


#转换item中的时间：01-Jan-1995
def date_convert(date):
    date = date.split('-')
    date_dict = {
        'Jan': 1,
        'Feb': 2,
        'Mar': 3,
        'Apr': 4,
        'May': 5,
        'Jun': 6,
        'Jul': 7,
        'Aug': 8,
        'Sep': 9,
        'Oct': 10,
        'Nov': 11,
        'Dec': 12,
    }
    return datetime.date(year=int(date[2]), month=date_dict[date[1]], day=int(date[0]))


#将user数据插入数据库
def data_into_user_sql():
    sep = r'|'
    users_file = 'data/ml-100k/u.user'
    users_headers = ['user id', 'age', 'gender', 'occupation', 'zip code']
    users_df = pd.read_csv(users_file, sep=sep, header=None,
                           names=users_headers, engine='python')
    gender_dict = {'M': 0., 'F': 1.}
    year_now = datetime.date.today().year
    for id, row in users_df.iterrows():
        age = row['age']
        year = year_now-age
        month = random.randint(1, 12)
        day = random.randint(1, 28)
        date_born = datetime.date(year=year, month=month, day=day)
        password = 'abc123456'
        password = make_password(password)
        email = str(id)+'@mail.com'
        user = User(username=str(id),
                    password=password,
                    email=email,
                    date_born=date_born,
                    gender=gender_dict[row['gender']],
                    occupation=row['occupation']
                    )
        user.save()


#将item数据插入数据库
def data_into_item_sql():
    pd.set_option('max_colwidth', 100)
    sep = r'|'
    movie_file = 'data/ml-100k/u.item'
    movie_url = 'data/ml-100k/movie_url.csv'
    movie_poster = 'data/ml-100k/movie_poster.csv'
    movie_headers = ['movie id', 'movie title', 'release date', 'video release date',
                     'IMDb URL', 'unknown', 'Action', 'Adventure', 'Animation',
                     'Childrens', 'Comedy', 'Crime', 'Documentary', 'Drama', 'Fantasy',
                     'Film-Noir', 'Horror', 'Musical', 'Mystery', 'Romance', 'Sci-Fi',
                     'Thriller', 'War', 'Western']
    url_headers = ['movie id', 'url']
    poster_headers = ['movie id', 'poster_url']
    movie_df = pd.read_csv(movie_file, sep=sep, header=None,
                           names=movie_headers, engine='python')
    url_df = pd.read_csv(movie_url, names=url_headers, engine='python')
    poster_df = pd.read_csv(movie_poster, names=poster_headers)
    url_all_df = pd.merge(url_df, poster_df, how='left', left_on='movie id', right_on='movie id')

    movie_df = pd.merge(url_all_df, movie_df, how='left', left_on='movie id', right_on='movie id')

    df_index = ['movie id', 'url',  'poster_url', 'movie title', 'release date', 'Action',
                'Adventure', 'Animation', 'Childrens', 'Comedy', 'Crime', 'Documentary',
                'Drama', 'Fantasy', 'Film-Noir', 'Horror', 'Musical', 'Mystery', 'Romance',
                'Sci-Fi', 'Thriller', 'War', 'Western']
    movie_df = movie_df[df_index].copy()

    genre_headers = movie_df.columns.values[5:]
    num_genres = genre_headers.shape[0]

    for id, df in movie_df.iterrows():
        url = df['url']
        poster_url = df['poster_url']
        title = df['movie title']
        date = date_convert(df['release date'])
        string = ''
        df_ = df[genre_headers]
        for i in range(num_genres):
            string = string+str(df_[i])
        movie = Movie(title=title,
                      release_date=date,
                      genre=string,
                      imdb_url=url,
                      poster_url=poster_url
                      )
        movie.save()


#将rating数据插入数据库
def data_into_rating_sql():
    sep = '\t'
    path = 'data/ml-100k/u.data'
    rating_headers = ['user_id', 'movie_id', 'rating', 'timestamp']
    rating_df = pd.read_csv(path, sep=sep, header=None, names=rating_headers)
    rating_df = rating_df.sort_values(['user_id', 'movie_id']).reset_index(drop=True)
    for id,df in rating_df.iterrows():
        user_id = int(df['user_id'])
        movie_id = int(df['movie_id'])
        rating_num = int(df['rating'])
        user = User.objects.get(id=user_id)
        movie = Movie.objects.get(id=movie_id)
        rating = Rating(rating=rating_num)
        rating.user_id = user
        rating.movie_id = movie
        rating.save()




